﻿Imports Microsoft.VisualBasic
Imports Microsoft.VisualBasic.CompilerServices
Imports System
Imports System.Data.OleDb
Imports System.Reflection
Imports Microsoft.Office.Interop.Excel

Namespace ReportExcel
    Public Class clsGroup
        ' Methods
        Public Sub New(ByRef xlTSheet1 As Worksheet, ByRef xlSheet1 As Worksheet, ByRef rs1 As OleDbDataReader, ByVal intTemplateRow1 As Integer, ByVal intColCount1 As Integer, ByVal intDetailX1 As Integer)
            Me.xlTSheet = xlTSheet1
            Me.xlSheet = xlSheet1
            Me.rs = rs1
            Me.intTemplateRow = intTemplateRow1
            Me.intCRow = intTemplateRow1
            Me.intDetailX = intDetailX1
            Me.intColCount = intColCount1
            Me.vtFieldList = New Collection((Me.intColCount + 1) - 1) {}
            Dim intColCount As Integer = Me.intColCount
            Dim i As Integer = 1
            Do While (i <= intColCount)
                Me.vtFieldList(i) = New Collection
                i += 1
            Loop
            Me.vtTemplateFormula = New Collection
            Me.vtTemplateFormula1 = New Collection
            Me.vtFieldValueList = New String((Me.intColCount + 1) - 1) {}
            Me.parseGroupTemplate()
        End Sub

        Public Sub New(ByRef xlTSheet1 As Worksheet, ByRef xlSheet1 As Worksheet, ByRef rs1 As OleDbDataReader, ByVal strGroupFieldName1 As String, ByVal intTemplateRow1 As Integer, ByVal intColCount1 As Integer, ByVal intDetailX1 As Integer)
            Me.xlTSheet = xlTSheet1
            Me.xlSheet = xlSheet1
            Me.rs = rs1
            Me.strGroupFieldName = strGroupFieldName1
            Me.intTemplateRow = intTemplateRow1
            Me.intCRow = intTemplateRow1
            Me.intDetailX = intDetailX1
            Me.intColCount = intColCount1
            Me.vtFieldList = New Collection((Me.intColCount + 1) - 1) {}
            Dim intColCount As Integer = Me.intColCount
            Dim i As Integer = 1
            Do While (i <= intColCount)
                Me.vtFieldList(i) = New Collection
                i += 1
            Loop
            Me.vtTemplateFormula = New Collection
            Me.vtTemplateFormula1 = New Collection
            Me.vtFieldValueList = New String((Me.intColCount + 1) - 1) {}
            Me.parseGroupTemplate()
        End Sub

        Public Sub clearPreviousGroupID()
            Me.strPGroupValue = ""
        End Sub

        Public Sub fillFormula(ByVal intRow As Integer)
            If (Me.intCRow <> Me.intPRow) Then
                Dim num2 As Integer = Me.vtTemplateFormula.Count
                Dim i As Integer = 1
                Do While (i <= num2)
                    Dim formula As clsFormula = DirectCast(Me.vtTemplateFormula.Item(i), clsFormula)
                    Dim range As Range = DirectCast(Me.xlSheet.Cells._Default(Me.intPRow, formula.getC), Range)
                    range.Formula = formula.GetFormula3((Me.intPRow + 1), (intRow - 1))
                    i += 1
                Loop
            End If
        End Sub

        Public Sub FillFormula1(ByVal intRow As Integer)
            Dim num2 As Integer = Me.vtTemplateFormula1.Count
            Dim i As Integer = 1
            Do While (i <= num2)
                Dim formula As clsFormula = DirectCast(Me.vtTemplateFormula.Item(i), clsFormula)
                Dim range As Range = DirectCast(Me.xlSheet.Cells._Default(intRow, formula.getC), Range)
                range.Formula = formula.GetFormula1(Me.rs)
                i += 1
            Loop
        End Sub

        Public Function FillGroup(ByVal intRow As Integer) As Boolean
            Dim flag As Boolean = False
            Dim str As String = Strings.Trim(StringType.FromObject(ObjectType.StrCatObj("", Me.rs.Item(Me.strGroupFieldName))))
            If (StringType.StrCmp(Me.strPGroupValue, str, False) = 0) Then
                Return flag
            End If
            Me.strPGroupValue = str
            Me.intPRow = Me.intCRow
            Me.intCRow = intRow
            Dim intCol As Integer = ((Me.intDetailX + Me.intColCount) - 1)
            Me.xlSheet.Range((Module1.ToLoc(intRow, Me.intDetailX) & ":" & Module1.ToLoc(intRow, intCol)), Missing.Value).Insert(Missing.Value)
            intCol = ((Me.intDetailX + Me.intColCount) - 1)
            Me.vtTemplateCell.Copy(Me.xlSheet.Range((Module1.ToLoc(intRow, Me.intDetailX) & ":" & Module1.ToLoc(intRow, intCol)), Missing.Value))
            Dim intColCount As Integer = Me.intColCount
            Dim i As Integer = 1
            Do While (i <= intColCount)
                Dim strValue As String = Module1.GetCellValueToFill(Me.rs, Me.vtFieldList(i), Me.vtFieldValueList(i))
                Module1.AddCellToSheet(Me.xlSheet, ((i + Me.intDetailX) - 1), intRow, strValue)
                i += 1
            Loop
            Me.FillFormula1(intRow)
            Return True
        End Function

        Public Function GetTemplateRow() As Integer
            Return Me.intTemplateRow
        End Function

        Private Sub parseGroupTemplate()
            Try
                Dim c As Range = DirectCast(Me.xlTSheet.Cells._Default(Me.intTemplateRow, Me.intDetailX), Range)
                Me.strGroupFieldName = c.Comment.Text(Missing.Value, Missing.Value, Missing.Value)
                c.ClearComments()
                Dim intCol As Integer = ((Me.intDetailX + Me.intColCount) - 1)
                Me.vtTemplateCell = Me.xlTSheet.Range((Module1.ToLoc(Me.intTemplateRow, Me.intDetailX) & ":" & Module1.ToLoc(Me.intTemplateRow, intCol)), Missing.Value)
                Dim intColCount As Integer = Me.intColCount
                Dim i As Integer = 1
                Do While (i <= intColCount)
                    c = DirectCast(Me.xlTSheet.Cells._Default(Me.intTemplateRow, ((i + Me.intDetailX) - 1)), Range)
                    If BooleanType.FromObject(c.HasFormula) Then
                        Dim strFormula As String = StringType.FromObject(c.Formula)
                        Dim formula As New clsFormula(c, strFormula)
                        If (Strings.InStr(1, strFormula, "${", 0) <> 0) Then
                            Me.vtTemplateFormula1.Add(formula, Nothing, Nothing, Nothing)
                        Else
                            Me.vtTemplateFormula.Add(formula, Nothing, Nothing, Nothing)
                        End If
                        Me.vtFieldValueList(i) = ""
                    Else
                        Dim str As String = StringType.FromObject(c.Value)
                        Dim num3 As Integer = 0
                        Me.vtFieldValueList(i) = str
                        Do While (1 <> 0)
                            Dim num2 As Integer = Strings.InStr((num3 + 1), str, "${", 0)
                            If (num2 = 0) Then
                                Exit Do
                            End If
                            num3 = Strings.InStr(num2, str, "}", 0)
                            If (num3 = 0) Then
                                Exit Do
                            End If
                            Dim str2 As String = Strings.Mid(str, (num2 + 2), ((num3 - num2) - 2))
                            Me.vtFieldList(i).Add(str2, Nothing, Nothing, Nothing)
                        Loop
                    End If
                    i += 1
                Loop
            Catch ex As Exception
                MessageBox.Show("Không tìm thấy field group trong templates" & ex.Message)
            End Try
        End Sub

        Public Sub unInit()
            Dim num As Integer
            Dim intColCount As Integer = Me.intColCount
            num = 1
            Do While (num <= intColCount)
                Dim i As Integer = Me.vtFieldList(num).Count
                Do While (i >= 1)
                    Me.vtFieldList(num).Remove(i)
                    i = (i + -1)
                Loop
                Me.vtFieldList(num) = Nothing
                num += 1
            Loop
            Me.vtFieldList = Nothing
            Me.vtTemplateCell = Nothing
            num = Me.vtTemplateFormula.Count
            Do While (num >= 1)
                Me.vtTemplateFormula.Remove(num)
                num = (num + -1)
            Loop
            Me.vtTemplateFormula = Nothing
            Me.vtFieldValueList = Nothing
        End Sub


        ' Fields
        Private intColCount As Integer
        Public intCRow As Integer
        Private intDetailX As Integer
        Public intPRow As Integer
        Private intTemplateRow As Integer
        Private rs As OleDbDataReader
        Private strGroupFieldName As String
        Private strPGroupValue As String
        Private vtFieldList As Collection()
        Private vtFieldValueList As String()
        Private vtTemplateCell As Range
        Private vtTemplateFormula As Collection
        Private vtTemplateFormula1 As Collection
        Private vtTemplateMerge As Collection
        Private xlSheet As Worksheet
        Private xlTSheet As Worksheet
    End Class
End Namespace

